package com.yuju.util;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Calendar;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

/**
 * 生成导出Excel文件对象
 * 
 * @author caihua
 * 
 */
public class ExcelExp {
	// 设置cell编码解决中文高位字节截断
	private static short XLS_ENCODING = HSSFCell.ENCODING_UTF_16;

	// 定制浮点数格式
	private static String NUMBER_FORMAT = "#,##0.00";

	// 定制日期格式
	private static String DATE_FORMAT = "m/d/yy"; // "m/d/yy h:mm"

	private OutputStream out = null;

	private SXSSFWorkbook workbook = null;

	private Sheet sheet = null;

	private Row row = null;
	
	private CellStyle setBorder = null;
	
	private CellStyle cellStyle = null;

	public ExcelExp() {
		this.workbook = new SXSSFWorkbook(500);//new HSSFWorkbook();
		this.sheet = workbook.createSheet();
		this.setBorder = this.workbook.createCellStyle();
	}

	/**
	 * 初始化Excel
	 * 
	 */
	public ExcelExp(String filePath) {
		try {
			this.out = new FileOutputStream(new File(filePath));
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		}
		this.workbook = new SXSSFWorkbook();
		this.sheet = workbook.createSheet();
	}

	/**
	 * 导出Excel文件
	 * 
	 * @throws IOException
	 */
	public void export() throws FileNotFoundException, IOException {
		try {
			workbook.write(out);
			out.flush();
			out.close();
		} catch (FileNotFoundException e) {
			throw new IOException(" 生成导出Excel文件出错! ", e);
		} catch (IOException e) {
			throw new IOException(" 写入Excel文件出错! ", e);
		}

	}

	/**
	 * 增加一行
	 * 
	 * @param index
	 *            行号
	 */
	public void createRow(int index) {
		this.row = this.sheet.createRow(index);
	}

	/**
	 * 获取单元格的值
	 * 
	 * @param index
	 *            列号
	 */
	public String getCell(int index) {
		Cell cell = this.row.getCell((short) index);
		String strExcelCell = "";
		if (cell != null) { // add this condition
			// judge
			switch (cell.getCellType()) {
			case HSSFCell.CELL_TYPE_FORMULA:
				strExcelCell = "FORMULA ";
				break;
			case HSSFCell.CELL_TYPE_NUMERIC: {
				strExcelCell = String.valueOf(cell.getNumericCellValue());
			}
				break;
			case HSSFCell.CELL_TYPE_STRING:
				strExcelCell = cell.getStringCellValue();
				break;
			case HSSFCell.CELL_TYPE_BLANK:
				strExcelCell = "";
				break;
			default:
				strExcelCell = "";
				break;
			}
		}
		return strExcelCell;
	}

	/**
	 * 设置单元格
	 * 
	 * @param index
	 *            列号
	 * @param value
	 *            单元格填充值
	 */
	public void setCell(int index, int value) {
		Cell cell = this.row.createCell((short) index);
		cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
		cell.setCellValue(value);
	}

	/**
	 * 设置单元格
	 * 
	 * @param index
	 *            列号
	 * @param value
	 *            单元格填充值
	 */
	public void setCell(int index, double value) {
		Cell cell = this.row.createCell((short) index);
		cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
		cell.setCellValue(value);
		CellStyle cellStyle = workbook.createCellStyle(); // 建立新的cell样式
		DataFormat format = workbook.createDataFormat();
		cellStyle.setDataFormat(format.getFormat(NUMBER_FORMAT)); // 设置cell样式为定制的浮点数格式
		cell.setCellStyle(cellStyle); // 设置该cell浮点数的显示格式
	}

	/**
	 * 设置单元格
	 * 
	 * @param index
	 *            列号
	 * @param value
	 *            单元格填充值
	 */
	public void setCell(int index, String value) {
		Cell cell = this.row.createCell((short) index);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		//cell.setEncoding(XLS_ENCODING);
		cell.setCellValue(value);
	}

	/**
	 * 设置单元格
	 * 
	 * @param index
	 *            列号
	 * @param value
	 *            单元格填充值
	 */
	public void setCell(int index, Calendar value) {
		Cell cell = this.row.createCell((short) index);
		//cell.setEncoding(XLS_ENCODING);
		cell.setCellValue(value.getTime());
		CellStyle cellStyle = workbook.createCellStyle(); // 建立新的cell样式
		cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(DATE_FORMAT)); // 设置cell样式为定制的日期格式
		cell.setCellStyle(cellStyle); // 设置该cell日期的显示格式'
	}

	public SXSSFWorkbook getWorkbook() {
		return workbook;
	}

	public void setWorkbook(SXSSFWorkbook workbook) {
		this.workbook = workbook;
	}
	
	/**
	 * 设置背景颜色
	 */
	public void setBackground() {
		this.setBorder.setFillBackgroundColor((short)20);
		this.setBorder.setFillPattern(FillPatternType.SOLID_FOREGROUND);
	}
	
	/**
	 * 设置边框
	 */
	public void setBorder() {
		this.setBorder.setBorderBottom(BorderStyle.HAIR); //下边框
		this.setBorder.setBorderLeft(BorderStyle.HAIR);//左边框
		this.setBorder.setBorderTop(BorderStyle.HAIR);//上边框
		this.setBorder.setBorderRight(BorderStyle.HAIR);//右边框
	}
	
	/**
	 * 设置居中
	 */
	public void setAlignCenter() {
		this.setBorder.setAlignment(HorizontalAlignment.CENTER); // 居中
	}
	
	/**
	 * 设置字体
	 */
	public void setFontStyle() {
		Font font = this.workbook.createFont();
		font.setFontName("黑体");
		font.setFontHeightInPoints((short) 20);//设置字体大小

		Font font2 = this.workbook.createFont();
		font2.setFontName("仿宋_GB2312");
		font2.setBold(true);
		//font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
		font2.setFontHeightInPoints((short) 16);

		setBorder.setFont(font);//选择需要用到的字体格式
	}
	
	/**
	 * 设置列宽
	 */
	public void setColumnWidth(int cellIndex, int width) {
		this.sheet.setColumnWidth(cellIndex, width); //第一个参数代表列id(从0开始),第2个参数代表宽度值  参考 ："2012-08-10"的宽度为2500
	}
	
	/**
	 * 设置自动换行
	 * @param flag
	 */
	public void setWrapText(boolean flag) {
		this.setBorder.setWrapText(flag);//设置自动换行
	}
	
	public void setAutoColumnSize() {
		sheet.autoSizeColumn(1, true);
	}
	
	public void setCellStyle(int rowIndex, int cellIndex, int fontSize, boolean isBold, boolean isCenter, boolean isBorder) {
		CellStyle style = workbook.createCellStyle();
		if(isBorder) {
		    style.setBorderTop(BorderStyle.DOTTED);
		    style.setBorderLeft(BorderStyle.DOTTED);
		    style.setBorderRight(BorderStyle.DOTTED);
		    style.setBorderBottom(BorderStyle.DOTTED);
		    style.setTopBorderColor(HSSFColor.BLACK.index);
		    style.setLeftBorderColor(HSSFColor.BLACK.index);
		}
	    Font font = this.workbook.createFont();
	    //font.setFontName("宋体");
	    font.setBold(isBold);
	    font.setFontHeightInPoints((short) fontSize);
	    style.setAlignment(isCenter ? HorizontalAlignment.CENTER : HorizontalAlignment.LEFT);
	    style.setFont(font);
	    this.sheet.getRow(rowIndex).getCell(cellIndex).setCellStyle(style);
	}

	public static void main(String[] args) {
		System.out.println(" 开始导出Excel文件 ");

		ExcelExp e = new ExcelExp("D://qt.xls");

		e.createRow(0);
		
		e.setCell(0, "商品名称");
		e.setCell(1, "采购价");
		e.setCell(2, "零售价");
		e.setCell(3, "会员价");
		e.setCell(4, "批发价");
		e.setCell(5, "市场价");
		e.setCell(6, "重量");
		e.setCell(7, "条码值");
		e.setCell(8, "规格型号");
		e.setCell(9, "厂家");
		e.setCell(10, "品牌");

		e.createRow(1);
		
		e.setCell(0, "商品A");
		e.setCell(1, 100);
		e.setCell(2, 110);
		e.setCell(3, 105);
		e.setCell(4, 103);
		e.setCell(5, 100);
		e.setCell(6, 10);
		e.setCell(7, "101010");
		e.setCell(8, "201010");
		e.setCell(9, "A厂家");
		e.setCell(10, "A品牌");
		
		e.createRow(2);
		
		e.setCell(0, "商品B");
		e.setCell(1, 100);
		e.setCell(2, 110);
		e.setCell(3, 105);
		e.setCell(4, 103);
		e.setCell(5, 100);
		e.setCell(6, 10);
		e.setCell(7, "101010");
		e.setCell(8, "201010");
		e.setCell(9, "B厂家");
		e.setCell(10, "B品牌");

		try {
			e.export();
			System.out.println(" 导出Excel文件[成功] ");
		} catch (IOException ex) {
			System.out.println(" 导出Excel文件[失败] ");
			ex.printStackTrace();
		}
	}
}
